﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace Ocmf.Types
{
    public static class DatabaseProvider
    {
        public static Dictionary<string, ScreenTemplate> ScreenTemplates { get; private set; }
        public static Dictionary<string, Experiment> Experiments { get; private set; }
        public static Dictionary<string, Chamber> Chambers { get; private set; }

        private static string localExperimentCachePath = Path.Combine(LocalSettings.ApplicationDataFolder, "Experiments");
        private static string localScreenTemplateCachePath = Path.Combine(LocalSettings.ApplicationDataFolder, "Screen Templates");

        private static MySqlConnection mySqlConnection = new MySqlConnection("SERVER=" + LocalSettings.DbServer + ";" +
                                               "DATABASE=" + LocalSettings.DbInstance + ";" +
                                               "UID=" + LocalSettings.DbUsername + ";" +
                                               "PASSWORD=" + LocalSettings.DbPassword + ";");

        static DatabaseProvider()
        {
            Experiments = new Dictionary<string, Experiment>();
            Chambers = new Dictionary<string, Chamber>();
            ScreenTemplates = new Dictionary<string, ScreenTemplate>();
        }

        public static void SynchronizeExperimentFolder()
        {
            try
            {
                //get db experiments (experimentId, lastSaved)
                var dbCommand = mySqlConnection.CreateCommand();

                var dbExperiments = new Dictionary<string, DateTime>();

                dbCommand.CommandText = "SELECT name, lastSaved FROM experiments";

                mySqlConnection.Open();

                var dbReader = dbCommand.ExecuteReader();

                while (dbReader.Read())
                    dbExperiments.Add((string)dbReader["name"], (DateTime)dbReader["lastSaved"]);

                mySqlConnection.Close();

                //get local experiments
                var localExperiments = new Dictionary<string, DateTime>();

                var experimentFiles = Directory.GetFiles(localExperimentCachePath, "*.experiment");

                foreach (var experimentFile in experimentFiles)
                    localExperiments.Add(Path.GetFileName(experimentFile), File.GetLastWriteTime(experimentFile));

                //update local from database - update files where experiments don't exist of the last write time is out of date
                foreach (var name in dbExperiments.Keys)
                    if (!localExperiments.ContainsKey(name) || localExperiments[name] < dbExperiments[name])
                    {
                        dbCommand.CommandText = "SELECT file, lastSaved FROM experiments WHERE name = @name";
                        dbCommand.Parameters.Clear();
                        dbCommand.Parameters.Add(new MySqlParameter("@name", name));

                        mySqlConnection.Open();

                        dbReader = dbCommand.ExecuteReader();

                        dbReader.Read();

                        File.WriteAllBytes(Path.Combine(localExperimentCachePath, name), (byte[])dbReader["file"]);
                        File.SetLastWriteTime(Path.Combine(localExperimentCachePath, name), (DateTime)dbReader["lastSaved"]);

                        mySqlConnection.Close();
                    }

                //update database from local
                foreach (var name in localExperiments.Keys)
                    if (!dbExperiments.ContainsKey(name))
                    {
                        dbCommand.CommandText = "INSERT INTO experiments VALUES (,@name,@file,@lastSaved)";
                        dbCommand.Parameters.Clear();
                        dbCommand.Parameters.Add(new MySqlParameter("@name", name));
                        dbCommand.Parameters.Add(new MySqlParameter("@file", File.ReadAllBytes(Path.Combine(localExperimentCachePath, name))));
                        dbCommand.Parameters.Add(new MySqlParameter("@lastSaved", File.GetLastWriteTime(Path.Combine(localExperimentCachePath, name))));

                        mySqlConnection.Open();

                        dbCommand.ExecuteNonQuery();

                        mySqlConnection.Close();
                    }
                    else if (dbExperiments[name] < localExperiments[name])
                    {
                        dbCommand.CommandText = "UPDATE experiments SET file = @file, lastSaved = @lastSaved WHERE name = @name";
                        dbCommand.Parameters.Add(new MySqlParameter("@name", File.ReadAllBytes(Path.Combine(localExperimentCachePath, name))));
                        dbCommand.Parameters.Add(new MySqlParameter("@lastSaved", localExperiments[name]));

                        mySqlConnection.Open();

                        dbCommand.ExecuteNonQuery();

                        mySqlConnection.Close();
                    }

                //recompile experiments dictionary
                experimentFiles = Directory.GetFiles(localExperimentCachePath, "*.experiment");
                Experiments.Clear();
                foreach (var experimentFile in experimentFiles)
                    Experiments.Add(Path.GetFileName(experimentFile), Experiment.LoadProperties(experimentFile));
            }
            catch (Exception exception)
            {
                MessageBox.Show("Failed to synchronize experiments folder with database:\r\n" + exception.Message + "\r\n OCMF Client will now switch to offline mode.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                //take client offline
                LocalSettings.DbOnline = false;
                LocalSettings.SaveToRegistry();
            }
        }

        public static void DeleteExperiment(Experiment experiment)
        {
            var dbCommand = mySqlConnection.CreateCommand();
            dbCommand.CommandText = "DELETE FROM experiments WHERE name = @name";
            dbCommand.Parameters.Add(new MySqlParameter("@name", experiment.FileName));

            mySqlConnection.Open();

            dbCommand.ExecuteNonQuery();

            mySqlConnection.Close();
        }

        public static void RefreshChambers()
        {

            try
            {
                var dbCommand = mySqlConnection.CreateCommand();
                dbCommand.CommandText = "SELECT * FROM chambers";

                mySqlConnection.Open();

                var dbReader = dbCommand.ExecuteReader();

                Chambers.Clear();

                while (dbReader.Read())
                    if (dbReader["runningExperimentName"].ToString() != "")
                        Chambers.Add(dbReader["hostName"].ToString(),
                                     new Chamber(long.Parse(dbReader["chamberId"].ToString()),
                                                 dbReader["hostName"].ToString(), dbReader["ipAddress"].ToString(),
                                                 (ChamberState)
                                                 Enum.Parse(typeof (ChamberState), dbReader["lastStatus"].ToString()),
                                                 DateTime.Parse(dbReader["lastUpdated"].ToString()),
                                                 Experiments[dbReader["runningExperimentName"].ToString()]));
                    else
                        Chambers.Add(dbReader["hostName"].ToString(),
                                     new Chamber(long.Parse(dbReader["chamberId"].ToString()),
                                                 dbReader["hostName"].ToString(), dbReader["ipAddress"].ToString(),
                                                 (ChamberState)
                                                 Enum.Parse(typeof (ChamberState), dbReader["lastStatus"].ToString()),
                                                 DateTime.Parse(dbReader["lastUpdated"].ToString()), null));

                mySqlConnection.Close();
            }
            catch (Exception exception)
            {
                MessageBox.Show("Failed to load chamber information from database:\r\n" + exception.Message + "\r\n OCMF Client will now switch to offline mode.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                //take client offline
                LocalSettings.DbOnline = false;
                LocalSettings.SaveToRegistry();
            }
        }

        public static void InsertChamber(Chamber chamber)
        {
            var dbCommand = mySqlConnection.CreateCommand();
            dbCommand.CommandText = "INSERT INTO chambers VALUES(null,@hostName,@lastStatus,@lastUpdated,@ipAddress,@upSince)";
            dbCommand.Parameters.Add("@hostName", chamber.HostName);
            dbCommand.Parameters.Add("@lastStatus", Enum.GetName(typeof(ChamberState),chamber.LastKnownStatus));
            dbCommand.Parameters.Add("@lastUpdated", chamber.LastSeenAlive);
            dbCommand.Parameters.Add("@ipAddress", chamber.IpAddress);
            dbCommand.Parameters.Add("@upSince", DateTime.Now);

            mySqlConnection.Open();

            dbCommand.ExecuteNonQuery();

            mySqlConnection.Close();

            //get back the db
        }

        public static void UpdateChamber(Chamber editChamber)
        {
            var dbCommand = mySqlConnection.CreateCommand();
            dbCommand.CommandText = "UPDATE chambers SET hostName = @hostName, ipAddress = @ipAddress, runningExperimentName = @runningExperimentName WHERE chamberId = @chamberId";
            dbCommand.Parameters.Add(new MySqlParameter("@hostName", editChamber.HostName));
            dbCommand.Parameters.Add(new MySqlParameter("@ipAddress", editChamber.IpAddress));
            dbCommand.Parameters.Add(new MySqlParameter("@chamberId", editChamber.ChamberId));
            if(editChamber.RunningExperiment != null)
                dbCommand.Parameters.Add(new MySqlParameter("@runningExperimentName", editChamber.RunningExperiment.FileName));
            else
                dbCommand.Parameters.Add(new MySqlParameter("@runningExperimentName", ""));

            mySqlConnection.Open();

            dbCommand.ExecuteNonQuery();

            mySqlConnection.Close();
        }

        public static void RefreshScreenTemplates()
        {
            try
            {
                //get db experiments (experimentId, lastSaved)
                var dbCommand = mySqlConnection.CreateCommand();

                var dbScreenTemplates = new Dictionary<string, DateTime>();

                dbCommand.CommandText = "SELECT name, lastSaved FROM screentemplates";

                mySqlConnection.Open();

                var dbReader = dbCommand.ExecuteReader();

                while (dbReader.Read())
                    dbScreenTemplates.Add((string)dbReader["name"], (DateTime)dbReader["lastSaved"]);

                mySqlConnection.Close();

                //get local experiments
                var localScreenTemplates = new Dictionary<string, DateTime>();

                var screenTemplateFiles = Directory.GetFiles(localScreenTemplateCachePath, "*.ost");

                foreach (var screenTemplateFile in screenTemplateFiles)
                    localScreenTemplates.Add(Path.GetFileName(screenTemplateFile), File.GetLastWriteTime(screenTemplateFile));

                //update local from database - update files where experiments don't exist of the last write time is out of date
                foreach (var name in dbScreenTemplates.Keys)
                    if (!localScreenTemplates.ContainsKey(name) || localScreenTemplates[name] < dbScreenTemplates[name])
                    {
                        dbCommand.CommandText = "SELECT file, lastSaved FROM screentemplates WHERE name = @name";
                        dbCommand.Parameters.Clear();
                        dbCommand.Parameters.Add(new MySqlParameter("@name", name));

                        mySqlConnection.Open();

                        dbReader = dbCommand.ExecuteReader();

                        dbReader.Read();

                        File.WriteAllBytes(Path.Combine(localScreenTemplateCachePath, name), (byte[])dbReader["file"]);
                        File.SetLastWriteTime(Path.Combine(localScreenTemplateCachePath, name), (DateTime)dbReader["lastSaved"]);

                        mySqlConnection.Close();
                    }

                //update database from local
                foreach (var name in localScreenTemplates.Keys)
                    if (!dbScreenTemplates.ContainsKey(name))
                    {
                        dbCommand.CommandText = "INSERT INTO screentemplates VALUES (,@name,@file,@lastSaved)";
                        dbCommand.Parameters.Clear();
                        dbCommand.Parameters.Add(new MySqlParameter("@name", name));
                        dbCommand.Parameters.Add(new MySqlParameter("@file", File.ReadAllBytes(Path.Combine(localScreenTemplateCachePath, name))));
                        dbCommand.Parameters.Add(new MySqlParameter("@lastSaved", File.GetLastWriteTime(Path.Combine(localScreenTemplateCachePath, name))));

                        mySqlConnection.Open();

                        dbCommand.ExecuteNonQuery();

                        mySqlConnection.Close();
                    }
                    else if (dbScreenTemplates[name] < localScreenTemplates[name])
                    {
                        dbCommand.CommandText = "UPDATE screentemplates SET file = @file, lastSaved = @lastSaved WHERE name = @name";
                        dbCommand.Parameters.Add(new MySqlParameter("@name", File.ReadAllBytes(Path.Combine(localScreenTemplateCachePath, name))));
                        dbCommand.Parameters.Add(new MySqlParameter("@lastSaved", localScreenTemplates[name]));

                        mySqlConnection.Open();

                        dbCommand.ExecuteNonQuery();

                        mySqlConnection.Close();
                    }

                //recompile experiments dictionary
                screenTemplateFiles = Directory.GetFiles(localScreenTemplateCachePath, "*.ost");
                Experiments.Clear();
                foreach (var screenTemplateFile in screenTemplateFiles)
                    ScreenTemplates.Add(Path.GetFileName(screenTemplateFile), ScreenTemplate.LoadProperties(screenTemplateFile));
            }
            catch (Exception exception)
            {
                MessageBox.Show("Failed to synchronize screen templates folder with database:\r\n" + exception.Message + "\r\n OCMF Client will now switch to offline mode.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                //take client offline
                LocalSettings.DbOnline = false;
                LocalSettings.SaveToRegistry();
            }
        }

    }
}
